package cn.edu.qlnu.xky.test.dao;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import cn.edu.qlnu.xky.test.common.Page;
import cn.edu.qlnu.xky.test.model.Book;
import cn.edu.qlnu.xky.test.common.MsgExchage;


/**
 * 根据service传递的参数组合成sql语句
 * 不允许出现jdbc对象
 */
public class BookDao {

	BaseDao dao = null;
	
	public BookDao() {
		dao = new BaseDao();
	}
	public int addBook(Book b) {
		if (countByIsbn(b.getIsbn(), -1)>0) {
			return 0;
		}
		String sql = "insert INTO t_book(bookName,author,pubTime,isbn, count)"
				+ "values('"
				+b.getBookName()+"','"
				+b.getAuthor()+"','"
				+MsgExchage.chDate(b.getPubTime())+"','"
				+b.getIsbn()+"','"
				+b.getCount()+"')";
		return dao.doAddEditDelete(sql);
	}
	
	public int deleteBook(int id) {
		String sql = "delete from t_book where id="+id;
		return dao.doAddEditDelete(sql);
	}
	
	public int editBook(Book b) {
		if (countByIsbn(b.getIsbn(), b.getId())>0) {
			return 0;
		}
		String sql = "update t_book SET "
				+ "bookName='"+b.getBookName()+"',"
				+ "author='"+b.getAuthor()+"',"
				+ "pubTime='"+MsgExchage.chDate(b.getPubTime())+"',"
				+ "isbn='"+b.getIsbn()+"',"
				+ "count="+b.getCount()+" "
				+ "where id="+b.getId();
		return dao.doAddEditDelete(sql);
	}
	
	public Book searchById(int id) {		
		String sql = "select * from t_book where id="+id;
		List<Book> bs = searchBySql(sql);
		if(bs.size() == 0)
			return new Book();
		else
			return bs.get(0);
	}

	public int updateBookCount(int id, String type) {
		if (dao.isClosed()) {
			dao = new BaseDao();
		}
		String sql = "";
		if ("add".equals(type)) {
			sql = "update t_book set count=count+1 where id=" + id;
		}else if ("sub".equals(type)) {
			sql = "update t_book set count=count-1 where id=" + id;
		}
		return dao.doAddEditDelete(sql);
	}

	public int countByIsbn(String isbn, int id) {
		String sql = "select count(*) from t_book where isbn='"+isbn+"'";
		if (id > -1) {
			sql += "and id!=" + id;
		}
		return new BaseDao().searchCount(sql);
	}
	
	public Page<Book> searchByBookNamePage(String bookName,int pageNo){
		String whereClase = " where bookName like '%"+bookName+"%'";
		String sql = "select * from t_book "+whereClase;
		Page<Book> p = new Page<Book>();
		p.setPageNo(pageNo);
		List<Map<String,Object>> objs = dao.searchByPage(sql,pageNo);
		int count = new BaseDao().searchCount("select count(*) from t_book "+whereClase);
		p.setTotalCount(count);
		List<Book> bs = new ArrayList<Book>();
		Book b = null;
		for(Map<String,Object> obj : objs) {
			b = new Book();
			b.setPubTime((Date)obj.get("pubTime"));
			b.setBookName((String)obj.get("bookName"));
			b.setId((int)obj.get("id"));
			b.setAuthor((String)obj.get("author"));
			b.setIsbn((String)obj.get("isbn"));
			b.setCount((int)obj.get("count"));			
			bs.add(b);
		}
		p.setRecords(bs);
		return p;
	}
	
	private List<Book> searchBySql(String sql){
		List<Map<String,Object>> objs = dao.searchBySql(sql);
		List<Book> bs = new ArrayList<Book>();
		Book b = null;
		for(Map<String,Object> obj : objs) {
			b = new Book();
			b.setPubTime((Date)obj.get("pubTime"));
			b.setBookName((String)obj.get("bookName"));
			b.setId((int)obj.get("id"));
			b.setAuthor((String)obj.get("author"));
			b.setIsbn((String)obj.get("isbn"));
			b.setCount((int)obj.get("count"));
			bs.add(b);
		}
		return bs;
	}
}
